﻿using CloudDcPc.Entity;
using CloudDcPc.Util;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class OrderBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public OrderBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        /**
         * 点餐
         */
        public bool AddOrder(Order order,ObservableCollection<MyMenu> list)
        {
            string suffix = "";
            string sql = "insert into dc_order(order_num,tab_num,size,time_type,user_num,type,time,together_name,together_price) values('" + order.Order_num + "','" + order.Tab_num + "','" + order.Size + "','" + order.Time_type + "','" + order.User_num + "',1,NOW(),'" + order.Together_name + "','" + order.Together_price + "')";
            string sql1 = "",sql2="";
            Orders os = null;
            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                sql2 = "update dc_tab set status=3 where tab_num='" + order.Tab_num + "'";
                cmd = new MySqlCommand(sql2, myConn);
                cmd.ExecuteNonQuery();
                foreach(MyMenu m in list){
                    os = new Orders()
                    {
                        Order_num = order.Order_num,
                        Food_name = m.Food_name,
                        Price = m.Price,
                        Number = float.Parse(m.CountInTg),
                        Taste = m.Taste,
                        Final_price = float.Parse(m.CountInTg)*m.Price,
                        Type = m.Type,
                        Suffix = m.Suffix
                    };
                    if(os.Suffix=="" || os.Suffix==null)
                        sql1 = "insert into dc_orders(order_num,food_name,price,number,taste,final_price,type) values('" + os.Order_num + "','" + os.Food_name + "','" + os.Price + "','" + os.Number + "','" + os.Taste + "','" + os.Final_price + "','" + os.Type + "')";
                    else
                        sql1 = "insert into dc_orders(order_num,food_name,price,number,taste,final_price,type,suffix) values('" + os.Order_num + "','" + os.Food_name + "','" + os.Price + "','" + os.Number + "','" + os.Taste + "','" + os.Final_price + "','" + os.Type + "','" + os.Suffix + "')";
                    
                    cmd = new MySqlCommand(sql1, myConn);
                    cmd.ExecuteNonQuery();        
                }
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {

                dbc.close();
            }
        }

        public bool DelOrderByOrderNum(string order_num, int tab_num)
        {
            string sql = "delete from dc_order where order_num='" + order_num + "'";
            string sql1 = "delete from dc_orders where order_num='" + order_num + "'";
            string sql2 = "update dc_tab set status=1 where tab_num='" + tab_num + "'";
            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand(sql1, myConn);
                cmd.ExecuteNonQuery();
                cmd = new MySqlCommand(sql2, myConn);
                cmd.ExecuteNonQuery();
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {

                dbc.close();
            }


        }
        public int GetNowOrderIdByTabNum(int tab_num)
        {
            int id = 0;
            string sql = "select id from dc_order where tab_num=" + tab_num + " and time is not null and haschecked=0";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    id = (int)reader["id"];
                }
                return id;
            }
            finally
            {
                dbc.close();
            }

        }

        public int GetNowOrderIdByTabName(string tab_name)
        {
            int id = 0;
            string sql = "select id from dc_order where tab_name=" + tab_name + " and time is not null and haschecked=0";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    id = (int)reader["id"];
                }
                return id;
            }
            finally
            {
                dbc.close();
            }

        }
        public bool HasYd(Order order)
        {
            string sql = "select id from dc_order where tab_num='" + order.Tab_num + "' and haschecked=0 and book_time='" + order.Book_time + "' and time_type='" + order.Time_type + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool AddYd(Order order)
        {
            string sql = "insert into dc_order(order_num,tab_num,size,time_type,book_time,user_num,note,type) values('" + order.Order_num + "','" + order.Tab_num + "','" + order.Size + "','" + order.Time_type + "','" + order.Book_time + "','" + order.User_num + "','" + order.Note + "',2)";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public List<Order> GetUncheckedOrderByTabNum(int tab_num)
        {
            List<Order> list = new List<Order>();
            string sql = "";
            sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.tab_num=" + tab_num + " and do.haschecked=0 order by book_time";
            Order order = null;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    order = BeanHelper.MakeOrder(reader);
                    list.Add(order);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }

        }

        public List<Order> GetOrderByTabStatus(int status)
        {
            List<Order> list = new List<Order>();
            string sql = "";
            if (status != 0)
                sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where dt.status=" + status + " and do.haschecked=0 and wait='0'";
            else
                sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.haschecked=0 and wait='0'";
            Order order = null;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    order = BeanHelper.MakeOrder(reader);
                    list.Add(order);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }

        }

        public List<Order> GetOrderById(List<int> ids)
        {
            string w = "";
            for (int i = 0; i < ids.Count; i++)
            {
                w += ids[i] + "";
                if (i != ids.Count - 1)
                    w += ",";
            }
            List<Order> list = new List<Order>();
            Order o = null;
            string sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.id in(" + w + ")";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    o = BeanHelper.MakeOrder(reader);
                    list.Add(o);
                }
                return list;
            }
            finally
            {
                dbc.close();
            }

        }
        public Order GetOrderById(int id)
        {
            Order o = null;
            string sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.id=" + id;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    o = BeanHelper.MakeOrder(reader);
                }
                return o;
            }
            finally
            {
                dbc.close();
            }

        }
        public Order GetOrderByOrderNum(string order_num)
        {
            Order order = null;
            string sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_order do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.order_num=" + order_num;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    order = BeanHelper.MakeOrder(reader);
                }
                return order;
            }
            finally
            {
                dbc.close();
            }

        }
        public Order GetHisOrderByOrderNum(string order_num)
        {
            Order order = null;
            string sql = "select do.*,dt.name,dt.size as tab_size,du.uname,dt.name as tab_name from dc_tab dt join dc_hisorder do on dt.tab_num=do.tab_num join dc_user du on du.user_num=do.user_num where do.order_num=" + order_num;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    order = BeanHelper.MakeOrder(reader);
                }
                return order;
            }
            finally
            {
                dbc.close();
            }

        }



    }
}
